IF OBJECT_ID('ssf.ptx_delete_task_group') IS NOT NULL
	DROP PROCEDURE ssf.ptx_delete_task_group
GO

CREATE PROCEDURE ssf.ptx_delete_task_group
	@group_type NVARCHAR(128) = N'DEFAULT'
	, @group_name NVARCHAR(128)
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Delete the task group and all of its tasks and dependencies.
	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@task_group_id INT
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@group_type, N'') IS NULL
OR NULLIF(@group_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[exit if task group does not exist]]></code_comment>*/
SET @task_group_id = ssf.ptx_get_task_group_id(@group_type, @group_name)

IF @task_group_id IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[begin transaction]]></code_comment>*/
BEGIN TRANSACTION

BEGIN TRY
-- ============

-- ============
/*<code_comment><![CDATA[delete dependencies]]></code_comment>*/
DELETE d
FROM ssf.ptx_task_dependency d
WHERE EXISTS (
	SELECT 1
	FROM ssf.ptx_get_task_dependency(@task_group_id)
	WHERE task_dependency_id = d.task_dependency_id
)
-- ============

-- ============
/*<code_comment><![CDATA[delete tasks]]></code_comment>*/
DELETE t
FROM ssf.ptx_task t
WHERE EXISTS (
	SELECT 1
	FROM ssf.ptx_get_task(@task_group_id)
	WHERE task_id = t.task_id
)
-- ============

-- ============
/*<code_comment><![CDATA[delete group]]></code_comment>*/
DELETE ssf.ptx_task_group
WHERE task_group_id = @task_group_id
-- ============

-- ============
/*<code_comment><![CDATA[end transaction]]></code_comment>*/
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
END CATCH

IF @sql_error <> 0
BEGIN
	ROLLBACK TRANSACTION
	
	RAISERROR('The DELETE of the task group failed:%s%s', 16, 1, @crlf, @error_message)
	RETURN @sql_error
END

COMMIT TRANSACTION
-- ============

RETURN @@ERROR
GO
